{\rtf1\ansi\ansicpg1252\cocoartf1038\cocoasubrtf360
{\fonttbl\f0\fswiss\fcharset0 Helvetica;\f1\fnil\fcharset0 Calibri;}
{\colortbl;\red255\green255\blue255;}
\paperw11900\paperh16840\margl1440\margr1440\vieww16120\viewh8760\viewkind0
\pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\ql\qnatural\pardirnatural

\f0\fs32 \cf0 CREATE DATABASE EPFMS;\
\
USE EPFMS;\
\
CREATE TABLE clients(\
client_id			INT UNSIGNED NOT NULL AUTO_INCREMENT,\
name				VARCHAR(40) NOT NULL,\
bday				DATE,\
address			TINYTEXT,\
cellPhone			CHAR(13),\
workPhone			CHAR(8),\
eMail				VARCHAR(20),\
ec_Name			VARCHAR(40),\
ec_Relationship	VARCHAR(20),\
ec_CellPhone		CHAR(13),\
ec_HomePhone	CHAR(8),\
p_Name			VARCHAR(40),\
p_Phone			CHAR(13),\
p_Fax				CHAR(13),\
referredBy			VARCHAR(40),\
dateRegistered		DATE,\
status				BOOLEAN DEFAULT '0',\
PRIMARY KEY (client_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE pastPresentHistory_items(\
pph_id		CHAR(6) NOT NULL,\
item		VARCHAR(255),\
PRIMARY KEY (pph_id)\
) ENGINE=InnoDB;\
\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_1', 'Rheumatic fever');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_2', 'Recent operation');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_3', 'Edema (swelling of ankles)');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_4', 'High blood pressure');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_5', 'Low blood pressure');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_6', 'Injury to back or knees');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_7', 'Seizures');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_8', 'Lung disease');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_9', 'Heart attack or known heart disease');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_10', 'Fainting or dizziness');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_11', 'Diabetes');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_12', 'High cholesterol');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_13', 'Orthopnea (the need to sit up to breathe comfortably) or Paroxysmal (sudden, unexpected attack) or Nocturnal Dyspnea (shortness of breath at night)');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_14', 'Shortness of breath at rest or with mild exertion');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_15', 'Chest pains');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_16', 'Palpitations or Tachycardia (unusually strong or rapid beat)');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_17', 'Intermittent claudication (calf cramping)');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_18', 'Pain, discomfort in the chest, neck, jaw, arms, or other areas');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_19', 'Known heart murmur');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_20', 'Unusual fatigue or shortness of breath with usual activities');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_21', 'Temporary loss of visual acuity or speech, or short-term numbness or weakness in one side, arm, or leg of your body');\
INSERT INTO pastPresentHistory_items (pph_id, item) VALUES ('pph_22', 'Cancer');\
\
CREATE TABLE pastPresentHistory_clients(\
client_id	INT UNSIGNED NOT NULL,\
pph_1		BOOLEAN DEFAULT '0',\
pph_2		BOOLEAN DEFAULT '0',\
pph_3		BOOLEAN DEFAULT '0',\
pph_4		BOOLEAN DEFAULT '0',\
pph_5		BOOLEAN DEFAULT '0',\
pph_6		BOOLEAN DEFAULT '0',\
pph_7		BOOLEAN DEFAULT '0',\
pph_8		BOOLEAN DEFAULT '0',\
pph_9		BOOLEAN DEFAULT '0',\
pph_10		BOOLEAN DEFAULT '0',\
pph_11		BOOLEAN DEFAULT '0',\
pph_12		BOOLEAN DEFAULT '0',\
pph_13		BOOLEAN DEFAULT '0',\
pph_14		BOOLEAN DEFAULT '0',\
pph_15		BOOLEAN DEFAULT '0',\
pph_16		BOOLEAN DEFAULT '0',\
pph_17		BOOLEAN DEFAULT '0',\
pph_18		BOOLEAN DEFAULT '0',\
pph_19		BOOLEAN DEFAULT '0',\
pph_20		BOOLEAN DEFAULT '0',\
pph_21		BOOLEAN DEFAULT '0',\
pph_22		BOOLEAN DEFAULT '0',\
others		VARCHAR(255),\
FOREIGN KEY (client_id) REFERENCES clients (client_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE familyHistory_items(\
fh_id	CHAR(5) NOT NULL,\
item	VARCHAR(255),\
PRIMARY KEY (fh_id)\
) ENGINE=InnoDB;\
\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_1', 'Heart attack');\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_2', 'Heart operation (Bypass surgery, Angioplasty, Coronary Stent Placement)');\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_3', 'Congenital heart disease');\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_4', 'High blood pressure');\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_5', 'High cholesterol');\
INSERT INTO familyHistory_items (fh_id, item) VALUES ('fh_6', 'Diabetes');\
\
CREATE TABLE familyHistory_clients(\
client_id	INT UNSIGNED NOT NULL,\
fh_1		BOOLEAN DEFAULT '0',\
fh_2		BOOLEAN DEFAULT '0',\
fh_3		BOOLEAN DEFAULT '0',\
fh_4		BOOLEAN DEFAULT '0',\
fh_5		BOOLEAN DEFAULT '0',\
fh_6		BOOLEAN DEFAULT '0',\
others		VARCHAR(255),\
explanation VARCHAR(255),\
FOREIGN KEY (client_id) REFERENCES clients (client_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE activityHistory_items(\
ah_id	CHAR(6) NOT NULL,\
item	VARCHAR(255),\
PRIMARY KEY (ah_id)\
) ENGINE=InnoDB;\
\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_1', '
\f1 How were you referred to this program?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_2', '
\f1 Why are you enrolling in this program?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_3', '
\f1 Have you ever worked with a personal trainer before?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_4', '
\f1 Date of your last physical examination performed by a physicain:
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_5a', '
\f1 Do you participate in a regular exercise program at this time?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_5b', '
\f1 If yes, briefly describe:
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_6', '
\f1 Can you currently walk 4 miles briskly without fatigue?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_7', '
\f1 Have you ever performed resistance training exercises in the past?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_8a', '
\f1 Do you have injuries (bone or muscle disabilities) that may interfere with exercising?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_8b', '
\f1 If yes, briefly describe:
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_9a', '
\f1 Do you smoke? If yes, how much per day and what was your age when you started?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_9b', '
\f1 Amount per day
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_9c', '
\f1 Age
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_10a', '
\f1 What is your body weight now?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_10b', '
\f1 What was it one year ago?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_10c', '
\f1 At age 21?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_10d', '
\f1 How tall are you?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_11', '
\f1 Do you follow or have you recently followed any specific dietary intake plan and, in general, how do you feel about your nutritional habits?
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_12', '
\f1 List the medications you are presently taking.
\f0 ');\
INSERT INTO activityHistory_items (ah_id, item) VALUES ('ah_13', '
\f1 List in order your personal health and fitness objectives.
\f0 ');\
\
CREATE TABLE activityHistory_clients(\
client_id	INT UNSIGNED NOT NULL,\
ah_1		TINYTEXT,\
ah_2		TINYTEXT,\
ah_3		BOOLEAN DEFAULT '0',\
ah_4		DATE,\
ah_5a		BOOLEAN DEFAULT '0',\
ah_5b		TINYTEXT,\
ah_6		BOOLEAN DEFAULT '0',\
ah_7		BOOLEAN DEFAULT '0',\
ah_8a		BOOLEAN DEFAULT '0',\
ah_8b		TINYTEXT,\
ah_9a		BOOLEAN DEFAULT '0',\
ah_9b		TINYINT(2) UNSIGNED,\
ah_9c		TINYINT(2) UNSIGNED,\
ah_10a		CHAR(7),\
ah_10b		CHAR(7),\
ah_10c		CHAR(7),\
ah_10d		CHAR(5),\
ah_11		VARCHAR(255),\
ah_12		VARCHAR(255),\
ah_13		VARCHAR(255),\
FOREIGN KEY (client_id) REFERENCES clients (client_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE assessment_items(\
assessment_id	INT UNSIGNED NOT NULL AUTO_INCREMENT,\
item			VARCHAR(255),\
PRIMARY KEY (assessment_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE assessment_clients(\
client_id		INT UNSIGNED NOT NULL,\
assessment_id	INT UNSIGNED NOT NULL,\
value			SMALLINT(3) UNSIGNED,\
unit				ENUM ('cm', 'in', 'm', 'ft', 'mm'),\
FOREIGN KEY (client_id) REFERENCES clients (client_id),\
FOREIGN KEY (assessment_id) REFERENCES assessment_items (assessment_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE management(\
admin_id	INT UNSIGNED NOT NULL AUTO_INCREMENT,\
name		VARCHAR(40),\
bday		DATE,\
status		BOOLEAN DEFAULT '1',\
PRIMARY KEY (admin_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE management_login(\
admin_id	INT UNSIGNED NOT NULL,\
username	VARCHAR(20) NOT NULL,\
password	VARCHAR(15) NOT NULL,\
permission	BOOLEAN NOT NULL,\
PRIMARY KEY (username),\
FOREIGN KEY (admin_id) REFERENCES management (admin_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE rates(\
rate_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,\
name		VARCHAR(30),\
fee			DECIMAL(5, 2),\
sessions	TINYINT(2) UNSIGNED,\
expiry		TINYINT(2) UNSIGNED,\
PRIMARY KEY (rate_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE enrollment(\
enroll_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,\
client_id		INT UNSIGNED NOT NULL,\
rate_id			INT UNSIGNED NOT NULL,\
start			DATE,\
end				DATE,\
session_count	TINYINT(2) UNSIGNED,\
actual_end		DATE,\
status			BOOLEAN DEFAULT '1',\
PRIMARY KEY (enroll_id),\
FOREIGN KEY (client_id) REFERENCES clients (client_id),\
FOREIGN KEY (rate_id) REFERENCES rates (rate_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE exercise_items(\
exercise_id	INT UNSIGNED NOT NULL AUTO_INCREMENT,\
item		VARCHAR(255),\
PRIMARY KEY (exercise_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE training_programs(\
program_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,\
client_id		INT UNSIGNED NOT NULL,\
sessions		TINYINT(2) UNSIGNED,\
session_count	TINYINT(2) UNSIGNED,\
status			BOOLEAN DEFAULT '0',\
PRIMARY KEY (program_id),\
FOREIGN KEY (client_id) REFERENCES clients (client_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE program_workouts(\
workout_id	INT UNSIGNED NOT NULL AUTO_INCREMENT,\
program_id	INT UNSIGNED NOT NULL,\
day			SET('M', 'T', 'W', 'Th', 'F', 'Sa', 'Su'),\
from_time	TIME,\
to_time		TIME,\
PRIMARY KEY (workout_id),\
FOREIGN KEY (program_id) REFERENCES training_programs (program_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE workout_exercises(\
workout_id	INT UNSIGNED NOT NULL,\
exercise_id	INT UNSIGNED NOT NULL,\
sets		TINYINT(1) UNSIGNED,\
reps		TINYINT(2) UNSIGNED,\
FOREIGN KEY (workout_id) REFERENCES program_workouts (workout_id),\
FOREIGN KEY (exercise_id) REFERENCES exercise_items (exercise_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE attendance(\
attendance_id	INT UNSIGNED NOT NULL AUTO_INCREMENT,\
admin_id		INT UNSIGNED NOT NULL,\
enroll_id		INT UNSIGNED NOT NULL,\
date			DATETIME,\
PRIMARY KEY (attendance_id),\
FOREIGN KEY (admin_id) REFERENCES management (admin_id),\
FOREIGN KEY (enroll_id) REFERENCES enrollment (enroll_id)\
) ENGINE=InnoDB;\
\
CREATE TABLE training_logs(\
attendance_id	INT UNSIGNED NOT NULL,\
program_id		INT UNSIGNED NOT NULL,\
workout_id		INT UNSIGNED NOT NULL,\
exercise_id		INT UNSIGNED NOT NULL,\
set_num		TINYINT(1) UNSIGNED,\
weight			SMALLINT(3) UNSIGNED,\
unit				ENUM('kg', 'lbs'),\
FOREIGN KEY (attendance_id) REFERENCES attendance (attendance_id),\
FOREIGN KEY (program_id) REFERENCES training_programs (program_id),\
FOREIGN KEY (workout_id) REFERENCES program_workouts (workout_id),\
FOREIGN KEY (exercise_id) REFERENCES exercise_items (exercise_id)\
) ENGINE=InnoDB;}